<?php
//connect to your database
mysql_connect("localhost:3306","root","expedia") or die("Unable to connect to MySQL ".mysql_error());
@mysql_select_db("eandb") or die( "Unable to select database ".mysql_error());
$term = trim(strip_tags($_GET['term']));//retrieve the search term that autocomplete sends
		$countryCode = "";
		$stateProvince = "";
	// 1 - city
	// 2 - state (count == 3) / country(count == 2)
	// 3 - country (count = 3)
	$searchString = explode(",", $term);
	if($searchString != false){
	
		if(count($searchString) == 2){
			$term = trim($searchString[0]);
			$countryCode = trim($searchString[1]);
			$stateProvince = $countryCode;
			$sql = "SELECT DISTINCT REPLACE(CONCAT_WS(', ',City,StateProvince,Country),', , ',', ') AS label FROM `activepropertylist` WHERE `SupplierType` = 'ESR' AND `City` Like '%".$term."%' AND (`StateProvince` Like '%".$stateProvince."%' OR `Country` Like '%".$countryCode."%') LIMIT 10";
		} else if(count($searchString) == 3){
			$term = trim($searchString[0]);
			$stateProvince = trim($searchString[1]);
			$countryCode = trim($searchString[2]);
			$sql = "SELECT DISTINCT REPLACE(CONCAT_WS(', ',City,StateProvince,Country),', , ',', ') AS label FROM `activepropertylist` WHERE `SupplierType` = 'ESR' AND `City` Like '%".$term."%' AND (`StateProvince` Like '%".$stateProvince."%' AND `Country` Like '%".$countryCode."%') LIMIT 10";
		} else {
			$stateProvince = "";
			$term = trim($searchString[0]);
			$countryCode = "";
			$sql = "SELECT DISTINCT REPLACE(CONCAT_WS(', ',City,StateProvince,Country),', , ',', ') AS label FROM `activepropertylist` WHERE `SupplierType` = 'ESR' AND `City` Like '%".$term."%' AND (`StateProvince` Like '%".$stateProvince."%' OR `Country` Like '%".$countryCode."%') LIMIT 10";
		}
	}

//$qstring = "SELECT DISTINCT City AS label FROM activepropertylist WHERE `SupplierType` = \"ESR\" AND City LIKE '%".$term."%' LIMIT 10";
$result = mysql_query($sql) or die(mysql_error()) ;//query the database for entries containing the term

while ($row = mysql_fetch_array($result,MYSQL_ASSOC))//loop through the retrieved values
{
	 $row['label']=htmlentities(stripslashes($row['label']));
     $row['category']="Locations:";
	 //$row['id']=(int)$row['id'];
	 $row_set[] = $row;//build an array
}
echo json_encode($row_set);//format the array into json data
mysql_close();
?>
